﻿using FlashPay.DAO.Interface;
using FlashPay.EF;
using FlashPay.EF.Models;
using FlashPay.Entity;
using FlashPay.Entity.Enum;
using FlashPay.Entity.Parameter;
using FlashPay.Entity.Request.DepositMatchRecord;
using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace FlashPay.DAO.Impl
{
    public class DepositMatchRecordDAOImpl : DepositMatchRecordDAO, IDisposable
    {
        #region 注入
        private FlashPayContext _context { set; get; }

        /// <summary>
        /// 日志模型
        /// </summary>
        private readonly LogRecord _logRecord;

        /// <summary>
        /// 日志模型
        /// </summary>
        private readonly LogDao _logDao;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public DepositMatchRecordDAOImpl(FlashPayContext context, LogRecord logRecord, LogDao logDao)
        {
            _context = context;
            _logRecord = logRecord;
            _logDao = logDao;
        }
        public void Dispose()
        {
            if (_context != null)
            {
                _context.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取订单月份
        /// </summary>
        /// <param name="orderNo">订单号</param>
        /// <returns></returns>
        private string GetOrderMonth(long orderNo)
        {
            return orderNo.ToString().Substring(4, 2);
        }

        /// <summary>
        /// 根据编号获取收款匹配记录
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>Company</returns>
        public DepositMatchRecord GetDepositMatchRecord(long id)
        {
            var tableName = $"DepositMatchRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(id);

            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositMatchRecord_{month}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE Id={id}";

            var depositMatchRecord = _context.DepositMatchRecord
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return depositMatchRecord;
        }

        /// <summary>
        /// 获取收款匹配记录列表
        /// </summary>
        public DataGrid<DepositMatchRecordResponse> GetDepositMatchRecordList(DepositMatchRecordQuery query)
        {
            var pList = new DataGrid<DepositMatchRecordResponse>()
            {
                Success = false
            };

            try
            {
                var companys = _context.Company.ToList();
                var sysconfigs = _context.SysConfig.Where(c => c.ConfigCode == "Transtype" && c.CompanyId == 2).ToList();

                var sql = string.Format("call sp_SelectDepositMatchRecord('{0}', '{1}', '{2}');", query.StartTime, query.EndTime, string.Join(", ", query.CompanyIds));

                var q = _context.DepositMatchRecord.FromSql(sql);

                q = q.Where(c => c.Status == (sbyte)1);
                //公司名称
                if (!string.IsNullOrEmpty(query.CompanyName))
                {
                    var companySearch = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).ToList();
                    q = q.Where(c => companySearch.Select(p => p.CompanyId).Contains(c.CompanyId));
                }
                //订单号
                if (!string.IsNullOrEmpty(query.OrderNo))
                {
                    q = q.Where(c => c.MatchOrderNo == long.Parse(query.OrderNo));
                }
                //银行名称
                if (!string.IsNullOrEmpty(query.ClientBankName))
                {
                    q = q.Where(c => c.ClientBankName.Contains(query.ClientBankName));
                }
                //用户名
                if (!string.IsNullOrEmpty(query.ClientAccountName))
                {
                    q = q.Where(c => c.ClientAccountName.Contains(query.ClientAccountName));
                }
                //商户单号
                if (!string.IsNullOrEmpty(query.ClientOrderNo))
                {
                    q = q.Where(c => c.ClientOrderNo.Contains(query.ClientOrderNo));
                }
                var list = q.OrderByDescending(r => r.Id).Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();

                #region 模型转换
                var dResponseList = new List<DepositMatchRecordResponse>();

                list.ForEach(item =>
                {
                    var dResponse = new DepositMatchRecordResponse();

                    #region 公司
                    var companyName = "";
                    if (companys != null && companys.Any())
                    {
                        var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                        if (company != null)
                        {
                            companyName = company.CompanyName;
                        }
                    }
                    #endregion

                    dResponse.Id = item.Id.ToString();
                    dResponse.CompanyId = item.CompanyId;
                    dResponse.CompanyName = companyName;
                    dResponse.BankCode = item.BankCode;
                    dResponse.MatchOrderNo = string.IsNullOrEmpty(item.MatchOrderNo.ToString()) ? null : item.MatchOrderNo.ToString();
                    dResponse.ClientOrderNo = item.ClientOrderNo;
                    dResponse.DepositDate = item.DepositDate;
                    dResponse.DepositAmount = item.DepositAmount.ToString("#0.00");
                    dResponse.ClientBankName = item.ClientBankName;
                    dResponse.ClientAccountName = item.ClientAccountName;
                    dResponse.ClientCardNumber = item.ClientCardNumber;
                    dResponse.PostScript = item.PostScript;
                    dResponse.Transtype = item.Transtype;
                    dResponse.DepositRemark = item.DepositRemark;
                    dResponse.DepositCardNumber = item.DepositCardNumber;//收款卡号

                    if (item.Transtype.HasValue)
                    {
                        var sysconfig = sysconfigs.FirstOrDefault(c => c.ConfigValue == item.Transtype);
                        if (sysconfig != null) {
                            dResponse.TranstypeName = sysconfig.ConfigContent;
                        }
                    }

                    //ts > 10 && ts <= 2880
                    dResponse.DepositDateTimeDifference = DateTime.Now.Subtract(item.DepositDate).TotalMinutes;
                    //查询按钮权限
                    if (query.UserPermission.Contains("ReceiptMatchList005"))
                    {
                        dResponse.QueryButtonPermission = "ReceiptMatchList005";
                    }
                    //补推送按钮权限及收款匹配记录
                    if (query.UserPermission.Contains("ReceiptMatchList006") && item.MatchOrderNo.HasValue)
                    {
                        dResponse.MakeUpPermission = "ReceiptMatchList006";
                        var result = GetDepositRecordByOrderNo(item.MatchOrderNo.Value);
                        if (!result)
                        {
                            dResponse.MakeUpPermission = "No DepositRecord";
                        }
                    }


                    dResponseList.Add(dResponse);
                });

                pList.Success = true;

                pList.Total = q.Count();
                pList.Rows = dResponseList;
                return pList;

                #endregion
            }
            catch (Exception ex)
            {
                //日志
                _logRecord.LogType = LogRecordLogType.DepositMatchRecord_Detail.GetHashCode();
                _logRecord.LogLevel = (sbyte)LogLevel.Error.GetHashCode();
                _logRecord.CreateDate = DateTime.Now;
                _logRecord.LogRemark = "收款匹配记录发生异常: " + ex.Message;
                _logDao.Insert(_logRecord);
                pList.ErrorMessage = "收款匹配记录发生异常";
                return pList;
            }
        }

        /// <summary>
        /// 查询收款匹配记录对应的收款记录
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        private bool GetDepositRecordByOrderNo(long orderNo)
        {
            var tableName = $"DepositRecord_{DateTime.Now.ToString("MM")}";

            var month = GetOrderMonth(orderNo);
            if (month != DateTime.Now.ToString("MM"))
            {
                tableName = $"DepositRecord_{month}";
            }

            var sql = $"select * from {tableName} where OrderNo={orderNo} and ((NoticeStatus={(sbyte)DepositRecordNoticeStatus.通知失败} and NoticeTimes=5) or (NoticeStatus={(sbyte)DepositRecordNoticeStatus.不通知} and NoticeTimes=0))";
            var depositRecords = _context.DepositRecord
                .FromSql(sql)
                .AsNoTracking()
                .ToList();
            if (depositRecords != null && depositRecords.Any())
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID查询详细信息
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DepositMatchRecord GetMatchRecordDetail(DepositMatchRecordQuery query)
        {
            //return _context.DepositMatchRecord.Where(e => e.Id.Equals(query.ID)).FirstOrDefault();

            var tableName = $"DepositMatchRecord_{DateTime.Now.ToString("MM")}";
            //var sql = $"SELECT Id,CompanyId,BankCode,DepositDate,ClientAccountName,DepositAmount,PostScript,ClientCardNumber,DepositCardNumber,Transtype FROM {tableName} WHERE Id={query.ID}";
            var sql = $"SELECT * FROM {tableName} WHERE Id={query.ID}";
            var paymentRecord = _context.DepositMatchRecord
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return paymentRecord;

        }

        /// <summary>
        /// 收款匹配记录[绑定收款记录]
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>

        public int ReceiptMatchRecordBindReceiptRecord(ReceiptMatchRecordBindReceiptRecordRequest request) {
            int rows = 0;
            try
            {
                var sql = $"call sp_UpdateMatchDepositRecordWeb('{request.OrderNo}','{request.Id}','{request.DepositRemark}');";

                var connection = _context.Database.GetDbConnection();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = CommandType.Text;
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    using (var updateResult = command.ExecuteReader())
                    {
                        if (updateResult.Read())
                        {
                            rows= updateResult.GetInt32(0);
                        }
                    }
                }
                return rows;
            }
            catch (Exception ex)
            {
                throw new Exception("匹配出错:"+ex.Message);
            }
        }

        /// <summary>
        /// 新增收款匹配记录
        /// </summary>
        /// <param name="request"></param>
        public JResult Add(AddRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            var tableName = $"DepositMatchRecord_{DateTime.Now.ToString("MM")}";

            try
            {
                #region 验证
                if (!request.CompanyId.HasValue)
                {
                    throw new Exception("请选择公司名称.");
                }

                if (string.IsNullOrEmpty(request.BankCode))
                {
                    throw new Exception("请选择收款银行编码.");
                }

                #region 商户单号
                if (string.IsNullOrEmpty(request.ClientOrderNo)) {
                    throw new Exception("请填写商户单号.");
                }

                var clientOrderNoReg = new Regex(@"^.{3,40}$");
                if (!clientOrderNoReg.IsMatch(request.ClientOrderNo))
                {
                    throw new Exception("商户单号必须介于 3 和 40 之间");
                }

                clientOrderNoReg = new Regex(@"^[A-Za-z0-9]+$");
                if (!clientOrderNoReg.IsMatch(request.ClientOrderNo))
                {
                    throw new Exception("商户单号由英文、数字组成.");
                }

                var sql = $"SELECT * FROM {tableName} WHERE CompanyId={request.CompanyId} and ClientOrderNo='ts_{request.ClientOrderNo}'";
                var paymentRecords = _context.DepositMatchRecord
                    .FromSql(sql)
                    .AsNoTracking()
                    .ToList();

                if (paymentRecords != null && paymentRecords.Any()) {
                    throw new Exception("商户单号已存在.");
                }
                #endregion

                //收款卡号
                if (string.IsNullOrEmpty(request.DepositCardNumber)) {

                    var depositCardNumberReg = new Regex(@"^.{12,20}$");
                    if (!depositCardNumberReg.IsMatch(request.DepositCardNumber))
                    {
                        throw new Exception("收款卡号必须介于 12 和 20 之间");
                    }
                }
                //订单时间
                if (string.IsNullOrEmpty(request.DepositDate))
                {
                    throw new Exception("订单时间不能为空.");
                }
                //订单金额
                if (string.IsNullOrEmpty(request.DepositAmount))
                {
                    throw new Exception("订单金额不能为空.");
                }

                var depositAmountReg = new Regex(@"^(([1-9]\d{0,3})|0)(?:\.[0-9]{1,4})?$");
                if (!depositAmountReg.IsMatch(request.DepositAmount))
                {
                    throw new Exception("限制订单金额最大保留四位小数，整数最多保留四位");
                }
                //客户银行
                if (request.ClientBankName.Equals("请选择"))
                {
                    request.ClientBankName = null;
                }
                //客户姓名
                if (!string.IsNullOrEmpty(request.ClientAccountName))
                {
                    var clientAccountNameReg = new Regex(@"^.{2,40}$");
                    if (!clientAccountNameReg.IsMatch(request.ClientAccountName))
                    {
                        throw new Exception("客户姓名必须介于 2 和 40 之间");
                    }
                }
                //客户卡号
                if (!string.IsNullOrEmpty(request.ClientCardNumber))
                {
                    var clientCardNumberReg = new Regex(@"^.{12,40}$");
                    if (!clientCardNumberReg.IsMatch(request.ClientCardNumber))
                    {
                        throw new Exception("客户卡号必须介于 12 和 20 之间");
                    }
                }
                //交易类型
                if (!request.Transtype.HasValue)
                {
                    throw new Exception("请选拔交易类型.");
                }
                #endregion

                var connection = _context.Database.GetDbConnection();

                using (var command = connection.CreateCommand())
                {
                    var clientOrderNo = $"ts_{request.ClientOrderNo}";

                    command.CommandText = "CALL sp_InsertDepositMatchRecordV3(@CompanyId, @BankCode, @ClientOrderNo, @DepositDate, @DepositAmount, @ClientBankName, @ClientAccountName, @ClientCardNumber, @PostScript, @TransType, @PayType, @DepositCardNumber);";
                    command.Parameters.Add(new MySqlParameter("CompanyId", request.CompanyId.Value));
                    command.Parameters.Add(new MySqlParameter("BankCode", request.BankCode));
                    command.Parameters.Add(new MySqlParameter("ClientOrderNo", clientOrderNo));
                    command.Parameters.Add(new MySqlParameter("DepositDate", request.DepositDate));
                    command.Parameters.Add(new MySqlParameter("DepositAmount", request.DepositAmount));
                    command.Parameters.Add(new MySqlParameter("ClientBankName", request.ClientBankName));
                    command.Parameters.Add(new MySqlParameter("ClientAccountName", request.ClientAccountName));
                    command.Parameters.Add(new MySqlParameter("ClientCardNumber", request.ClientCardNumber));
                    command.Parameters.Add(new MySqlParameter("PostScript", request.PostScript));
                    command.Parameters.Add(new MySqlParameter("TransType", request.Transtype));
                    command.Parameters.Add(new MySqlParameter("PayType", ""));
                    command.Parameters.Add(new MySqlParameter("DepositCardNumber", request.DepositCardNumber));
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    var updateStatus = 0;

                    using (var updateResult = command.ExecuteReader())
                    {
                        if (updateResult.Read())
                        {
                            updateStatus = updateResult.GetInt32(0);
                        }
                    }

                    if (updateStatus <= 0)
                    {
                        throw new Exception("操作失败：该付款单不满足重新付款");
                    }

                    response.Success = true;
                }
            }
            catch (Exception ex) {
                response.ErrorMessage = ex.Message;
            }

            return response;
        }

    }
}
